USE [Webcrm]
GO

/****** Object:  StoredProcedure [dbo].[QA_AGENT_WISE_Report]    Script Date: 12/4/2019 5:21:29 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROC [dbo].[QA_AGENT_WISE_Report]
@tlid VARCHAR(30), @agentid VARCHAR(30), @qaid VARCHAR(30), @from VARCHAR(10), @to VARCHAR(10), @reporttype VARCHAR(30),@Process_id VARCHAR(10),@UserId VARCHAR(10)
AS
BEGIN

SET NOCOUNT ON;


--drop Table #temp;
select Agent_Name,Avg(External_Performance)[CallScore],
CASE WHEN Avg(External_Performance)>=91 THEN 'A' WHEN Avg(External_Performance)>=86 and Avg(External_Performance)<=90 THEN 'B' ELSE 'Moderate' END [Category] 
INTO #temp
from QA_AgentDetails
group by Agent_Name 

--select * from QA_AgentDetails

SELECT Agent_Name,[CallScore],Category FROM #temp

DECLARE @TotalAgent INT=0
SELECT @TotalAgent=COUNT(1) FROM #temp

SELECT 'A (Good)' Call_Quality_Category,isnull(SUM(CASE WHEN CallScore>=91 THEN 1 ELSE 0 END),0) Number_OF_Agents,
isnull(((SUM(CASE WHEN CallScore>=91 THEN 1 ELSE 0 END)*100)/@TotalAgent),0) per_Contribution
FROM #temp where CallScore>=91
UNION ALL
SELECT 'B (Average)' Call_Quality_Category,isnull(SUM(CASE WHEN CallScore>=86 and CallScore<=90 THEN 1 ELSE 0 END),0) Number_OF_Agents,
isnull(((SUM(CASE WHEN CallScore>=86 and CallScore<=90 THEN 1 ELSE 0 END)*100)/@TotalAgent),0) per_Contribution
FROM #temp where CallScore>=86 and CallScore<=90
UNION ALL
SELECT 'C (Moderate)' Call_Quality_Category,isnull(SUM(CASE WHEN CallScore<86 THEN 1 ELSE 0 END),0) Number_OF_Agents,
isnull(((SUM(CASE WHEN CallScore<86 THEN 1 ELSE 0 END)*100)/@TotalAgent),0) per_Contribution
FROM #temp where CallScore<86


END


GO

/****** Object:  StoredProcedure [dbo].[QA_AuditWISE_REPORT]    Script Date: 12/4/2019 5:21:29 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROC [dbo].[QA_AuditWISE_REPORT]
@tlid VARCHAR(30), @agentid VARCHAR(30), @qaid VARCHAR(30), @from VARCHAR(10), @to VARCHAR(10), @reporttype VARCHAR(30),@Process_id VARCHAR(10),@UserId VARCHAR(10)
AS BEGIN

DECLARE @sql NVARCHAR(MAX)='';

SET @sql='select convert(varchar,Date_of_Audit,103)[Date], ql.Assign_To[QA Name],
count(Assign_To)[Total Lead Assign],
count(qa.Disposition)[Total leads audited],
(count(Assign_To)-count(qa.Disposition))[Audit pending],
sum(case when qa.Disposition=''Quality Approve'' then 1 else 0 end)[Audit ok],
sum(case when qa.Disposition=''Quality Rework'' then 1 else 0 end)[Rework],
sum(case when qa.Disposition=''Quality Reject'' then 1 else 0 end)[Reject]
from QA_AgentDetails qa 
join QA_Assign_Log ql on ql.Log_Id=qa.Log_Id
where P_id='''+@Process_id+'''  and isnull(ql.Assign_To,'''')!='''' and (Q_id in (select max(Q_id) from QA_AgentDetails   group by Log_Id ) OR Q_id is null) '
    IF(@tlid!='0')
    SET @sql+=' and qa.TLname='''+@tlid+'''';
    IF(@agentid!='0')
    SET @sql+=' and qa.Agent_Name='''+@agentid+'''';
    IF(@qaid!='0')
    SET @sql+='';

    SET @sql+=CASE WHEN @from='' THEN 'AND CONVERT(DATE,Date_of_Audit) BETWEEN CONVERT(DATE,GETDATE())' ELSE ' AND CONVERT(DATE,Date_of_Audit) BETWEEN '''+@from+''' ' END;
    SET @sql+=CASE WHEN @to='' THEN 'AND CONVERT(DATE,GETDATE())' ELSE ' AND '''+@to+''' ' END;

SET @sql+=' group by ql.Assign_To,convert(varchar,Date_of_Audit,103) ';
EXECUTE sp_executesql @SQL

END

--QA_AuditWISE_REPORT '0','0','0','2019-11-03','2019-11-06','0'

--select * from QA_Assign_Log


GO

/****** Object:  StoredProcedure [dbo].[QA_COUNT]    Script Date: 12/4/2019 5:21:29 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE proc [dbo].[QA_COUNT] @Pid varchar(10),@FromDate varchar(19)='',@ToDate varchar(19)='',@User_Type varchar(30),@QAAgent varchar(70)='',@Suserid varchar(40)=''

--QA_COUNT '1','','','Quality Manager','',''
--QA_COUNT '1','2019-11-01','','Quality Manager','',''
--QA_COUNT @Pid='1',@FromDate='',@ToDate='',@User_Type='',@QAAgent='',@Suserid=''
--QA_COUNT @Pid='1',@User_Type='M'
--QA_COUNT '1','','','TL','','SUmit'
--QA_COUNT '1','2019-11-07','','TL','','SUmit'
--QA_COUNT  @Pid=1,@FromDate='2019-11-07',@ToDate='2019-11-08',@User_Type='M',@QAAgent='--select--'
--QA_COUNT '1','','','A','','munish'
As begin



Declare @Sqlquery1 varchar(max),@Sqlquery2 varchar(max),@CusDispotion varchar(150),@Sqlquery3 varchar(max),@DataCon varchar(max),@DataCon1 varchar(max),@SqlCon varchar(max),@StartDateFrom varchar(19)--='2019-11-09'
	select @CusDispotion=''''+replace(CallStatus,',',''',''')+'''',@StartDateFrom=Convert(varchar(10),Qa_Start_Date ,121) from QA_Page_Master1 where Process_id=@Pid
	set @DataCon=' and process_' + @Pid + '_log.Start_time>='''+@StartDateFrom+''' '+case when @FromDate='' then '  and  process_' + @Pid + '_log.Start_time>='''+Convert(varchar(10),getdate(),121)+' 00:00:00''' else  ' and process_' + @Pid + '_log.Start_time>='''+@FromDate+''''  end+ case when @ToDate='' then ' and process_' + @Pid + '_log.Start_time<='''+Convert(varchar(10),getdate(),121)+' 23:59:59''' else  ' and process_' + @Pid + '_log.Start_time<='''+@ToDate+''''  end 
	set @DataCon1= ' and QA_Assign_Log.Assign_Date>='''+@StartDateFrom+''''  +case when @FromDate='' then ' and QA_Assign_Log.Assign_Date >='''+Convert(varchar(10),getdate(),121)+' 00:00:00''' else  ' and QA_Assign_Log.Assign_Date>='''+@FromDate+''''  end+ case when @ToDate='' then ' and QA_Assign_Log.Assign_Date<='''+Convert(varchar(10),getdate(),121)+' 23:59:59''' else  ' and QA_Assign_Log.Assign_Date<='''+@ToDate+''''  end 

	set @SqlCon=' and QA_AgentDetails.Date_of_Audit>='''+@StartDateFrom+''' '+ case when @FromDate='' then ' and  QA_AgentDetails.Date_of_Audit>='''+Convert(varchar(10),getdate(),121)+' 00:00:00''' else  ' and QA_AgentDetails.Date_of_Audit>='''+@FromDate+''''  end+ case when @ToDate='' then ' and QA_AgentDetails.Date_of_Audit<='''+Convert(varchar(10),getdate(),121)+' 23:59:59''' else  ' and QA_AgentDetails.Date_of_Audit<='''+@ToDate+''''  end 
	

	--select @CusDispotion,@StartDateFrom
	if @User_Type='Quality Manager'
	begin
	
	set @Sqlquery1='select count(1) [Total] from process_' + @Pid + '_master left join process_' + @Pid + '_log on process_' + @Pid + '_master.Master_Id=process_' + @Pid + '_log.Master_Id  where  CallStatus in('+@CusDispotion+') '+@DataCon+ case when @QAAgent='' or @QAAgent='--select--'  then ' ' else ' and QA_Assign_Agent='''+@QAAgent+'''' end +' ;
					select count(1) [Pending] from process_' + @Pid + '_master left join process_' + @Pid + '_log on process_' + @Pid + '_master.Master_Id=process_' + @Pid + '_log.Master_Id where CallStatus in('+@CusDispotion+')  AND process_' + @Pid + '_log.QA_Disposition is null and Start_time>='''+@StartDateFrom +''''+ case when @QAAgent='' or @QAAgent='--select--'  then ' and status is null ' else ' and status=''1'' and QA_Assign_Agent='''+@QAAgent+'''' end +' ;
	                select count(1)[Quality Approve] from process_' + @Pid + '_master left join process_' + @Pid + '_log on process_' + @Pid + '_master.Master_Id = process_' + @Pid + '_log.Master_Id left join QA_AgentDetails on process_' + @Pid + '_log.Log_id=QA_AgentDetails.Log_id  where Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and QA_Disposition = ''Quality Approve''' + @SqlCon+ case when @QAAgent=''or @QAAgent='--select--'  then ' ' else ' and QA_Assign_Agent='''+@QAAgent+'''' end +' ;
					select count(1) [Quality Rework] from process_' + @Pid + '_master left join process_' + @Pid + '_log on process_' + @Pid + '_master.Master_Id = process_' + @Pid + '_log.Master_Id left join QA_AgentDetails on process_' + @Pid + '_log.Log_id=QA_AgentDetails.Log_id where Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and QA_Disposition = ''Quality Rework''' + @SqlCon+ case when @QAAgent=''or @QAAgent='--select--'  then ' ' else ' and QA_Assign_Agent='''+@QAAgent+'''' end +' ;
					select count(1) [Quality Hold] from process_' + @Pid + '_master left join process_' + @Pid + '_log on process_' + @Pid + '_master.Master_Id = process_' + @Pid + '_log.Master_Id left join QA_AgentDetails on process_' + @Pid + '_log.Log_id=QA_AgentDetails.Log_id where Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and QA_Disposition = ''Quality Hold''' + @SqlCon+ case when @QAAgent=''or @QAAgent='--select--'  then ' ' else ' and QA_Assign_Agent='''+@QAAgent+'''' end +' ;
	                select count(1) [Quality Reject] from process_' + @Pid + '_master left join process_' + @Pid + '_log on process_' + @Pid + '_master.Master_Id = process_' + @Pid + '_log.Master_Id left join QA_AgentDetails on process_' + @Pid + '_log.Log_id=QA_AgentDetails.Log_id  where Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and QA_Disposition = ''Quality Reject''' + @SqlCon+ case when @QAAgent=''or @QAAgent='--select--'  then ' ' else ' and QA_Assign_Agent='''+@QAAgent+'''' end +' ;
				/*select count(1) [Quality Pending] from process_' + @Pid + '_master left join process_' + @Pid + '_log on process_' + @Pid + '_master.Master_Id = process_' + @Pid + '_log.Master_Id left join QA_AgentDetails on process_' + @Pid + '_log.Log_id=QA_AgentDetails.Log_id  where Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and QA_Disposition Is Null' + @SqlCon+ case when @QAAgent=''or @QAAgent='--select--'  then ' ' else ' and QA_Assign_Agent='''+@QAAgent+'''' end +' ;*/
					select count(1) [Quality Pending] from process_' + @Pid + '_master left join process_' + @Pid + '_log on process_' + @Pid + '_master.Master_Id=process_' + @Pid + '_log.Master_Id  where   QA_Disposition Is Null and CallStatus in('+@CusDispotion+') '+@DataCon+ case when @QAAgent='' or @QAAgent='--select--'  then ' ' else '  and QA_Assign_Agent='''+@QAAgent+'''' end +' ;
					select count(1) [TL Approve] from process_' + @Pid + '_master left join process_' + @Pid + '_log on process_' + @Pid + '_master.Master_Id = process_' + @Pid + '_log.Master_Id left join QA_AgentDetails on process_' + @Pid + '_log.Log_id=QA_AgentDetails.Log_id  where Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and QA_Disposition = ''TL Approve''' + @SqlCon+ case when @QAAgent=''or @QAAgent='--select--'  then ' ' else ' and QA_Assign_Agent='''+@QAAgent+'''' end +' ;
					select count(1) [TL Hold] from process_' + @Pid + '_master left join process_' + @Pid + '_log on process_' + @Pid + '_master.Master_Id = process_' + @Pid + '_log.Master_Id left join QA_AgentDetails on process_' + @Pid + '_log.Log_id=QA_AgentDetails.Log_id  where Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and QA_Disposition = ''TL Hold''' + @SqlCon+ case when @QAAgent=''or @QAAgent='--select--'  then ' ' else ' and QA_Assign_Agent='''+@QAAgent+'''' end +' ;
					select count(1) [TL Reject] from process_' + @Pid + '_master left join process_' + @Pid + '_log on process_' + @Pid + '_master.Master_Id = process_' + @Pid + '_log.Master_Id left join QA_AgentDetails on process_' + @Pid + '_log.Log_id=QA_AgentDetails.Log_id  where Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and QA_Disposition = ''TL Reject''' + @SqlCon+ case when @QAAgent=''or @QAAgent='--select--'  then ' ' else ' and QA_Assign_Agent='''+@QAAgent+'''' end +' ; '
	  -- exec  (@Sqlquery1)      
	end

else if @User_Type='Quality TL'
	begin
	Declare @TlId varchar(20)
	select @TlId=User_Id from User_Master where User_Name=@Suserid;
	 set @Sqlquery1='select count(1) [Total] from process_'+@Pid+'_master left join process_'+@Pid+'_log  on process_'+@Pid+'_master.Master_Id=process_'+@Pid+'_log.Master_Id left join QA_AgentDetails on process_'+@Pid+'_log.Log_id=QA_AgentDetails.Log_id where CallStatus in('+@CusDispotion+')  and (status is null or status=1) and (process_'+@Pid+'_log.QA_Disposition=''Quality Rework'' or  process_'+@Pid+'_log.QA_Disposition=''Quality Hold'') and (Q_id in (select max(Q_id) from QA_AgentDetails   group by Log_Id ) OR Q_id is null) and Team_Leader_Id='''+@TlId+'''' + @SqlCon+';
					 select count(1) [Pending] from process_'+@Pid+'_master left join process_'+@Pid+'_log  on process_'+@Pid+'_master.Master_Id=process_'+@Pid+'_log.Master_Id left join QA_AgentDetails on process_'+@Pid+'_log.Log_id=QA_AgentDetails.Log_id where CallStatus in('+@CusDispotion+') and (process_'+@Pid+'_log.QA_Disposition=''Quality Rework'' or  process_'+@Pid+'_log.QA_Disposition=''Quality Hold'') and (Q_id in (select max(Q_id) from QA_AgentDetails   group by Log_Id ) OR Q_id is null) and Team_Leader_Id='''+@TlId+''' and QA_AgentDetails.Date_of_Audit>='''+@StartDateFrom +''';
					 select count(1) [Quality Rework] from process_'+@Pid+'_master left join process_'+@Pid+'_log  on process_'+@Pid+'_master.Master_Id = process_'+@Pid+'_log.Master_Id left join QA_AgentDetails on process_'+@Pid+'_log.Log_id=QA_AgentDetails.Log_id  where process_'+@Pid+'_log.QA_Disposition = ''Quality Rework'' and Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and (process_'+@Pid+'_log.QA_Disposition=''Quality Rework'' or  process_'+@Pid+'_log.QA_Disposition=''Quality Hold'') and (Q_id in (select max(Q_id) from QA_AgentDetails   group by Log_Id ) OR Q_id is null) and Team_Leader_Id='''+ @TlId +'''' + @SqlCon+';
					 select count(1) [Quality Hold] from process_'+@Pid+'_master left join process_'+@Pid+'_log  on process_'+@Pid+'_master.Master_Id = process_'+@Pid+'_log.Master_Id left join QA_AgentDetails on process_'+@Pid+'_log.Log_id=QA_AgentDetails.Log_id  where process_'+@Pid+'_log.QA_Disposition = ''Quality Hold'' and Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and (process_'+@Pid+'_log.QA_Disposition=''Quality Rework'' or  process_'+@Pid+'_log.QA_Disposition=''Quality Hold'') and (Q_id in (select max(Q_id) from QA_AgentDetails   group by Log_Id ) OR Q_id is null) and Team_Leader_Id='''+@TlId+''''+ @SqlCon+';
					 select count(1) [TL Approve] from process_'+@Pid+'_master left join process_'+@Pid+'_log  on process_'+@Pid+'_master.Master_Id = process_'+@Pid+'_log.Master_Id left join QA_AgentDetails on  process_'+@Pid+'_log.Log_id=QA_AgentDetails.Log_id  where process_'+@Pid+'_log.QA_Disposition = ''TL Approve'' and Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and (Q_id in (select max(Q_id) from QA_AgentDetails   group by Log_Id ) OR Q_id is null) and Team_Leader_Id='''+@TlId+''''+ @SqlCon+';
					 select count(1) [TL Reject] from process_'+@Pid+'_master left join process_'+@Pid+'_log  on process_'+@Pid+'_master.Master_Id = process_'+@Pid+'_log.Master_Id left join QA_AgentDetails on process_'+@Pid+'_log.Log_id=QA_AgentDetails.Log_id  where process_'+@Pid+'_log.QA_Disposition = ''TL Reject'' and Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and (Q_id in (select max(Q_id) from QA_AgentDetails   group by Log_Id ) OR Q_id is null) and Team_Leader_Id='''+@TlId+''''+ @SqlCon+';
					 select count(1) [TL Hold] from process_'+@Pid+'_master left join process_'+@Pid+'_log  on process_'+@Pid+'_master.Master_Id = process_'+@Pid+'_log.Master_Id left join QA_AgentDetails on process_'+@Pid+'_log.Log_id=QA_AgentDetails.Log_id  where process_'+@Pid+'_log.QA_Disposition = ''TL Hold'' and Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and (Q_id in (select max(Q_id) from QA_AgentDetails   group by Log_Id ) OR Q_id is null) and Team_Leader_Id='''+@TlId+''''+ @SqlCon+';'
		--exec(@Sqlquery1)
	end
if @User_Type='Quality Agent'
	begin
	
	set @Sqlquery1='select count(1)[Total] from process_'+@Pid+'_master left join process_'+@Pid+'_log on process_'+@Pid+'_master.Master_Id=process_'+@Pid+'_log.Master_Id  join (select max(id)lastid,Log_Id from QA_Assign_Log group by Log_Id)LastAssign on  process_'+@Pid+'_log.Log_id=LastAssign.Log_id join QA_Assign_Log on QA_Assign_Log.id=LastAssign.lastid  where CallStatus in('+@CusDispotion+')  and status=1 and QA_Assign_Agent='''+@Suserid+''''+ @DataCon1+';
					select count(1)[Pending] from process_'+@Pid+'_master left join process_'+@Pid+'_log on process_'+@Pid+'_master.Master_Id=process_'+@Pid+'_log.Master_Id  join (select max(id)lastid,Log_Id from QA_Assign_Log group by Log_Id)LastAssign on  process_'+@Pid+'_log.Log_id=LastAssign.Log_id join QA_Assign_Log on QA_Assign_Log.id=LastAssign.lastid  where CallStatus in('+@CusDispotion+') and  (process_'+@Pid+'_log.QA_Disposition is null ) and QA_Assign_Agent='''+@Suserid+''' and QA_Assign_Log.Assign_Date>='''+@StartDateFrom +''' ;
					select count(1)[Quality Approve] from process_'+@Pid+'_master left join process_'+@Pid+'_log on process_'+@Pid+'_master.Master_Id = process_'+@Pid+'_log.Master_Id   join (select max(id)lastid,Log_Id from QA_Assign_Log group by Log_Id)LastAssign on  process_'+@Pid+'_log.Log_id=LastAssign.Log_id join QA_Assign_Log on QA_Assign_Log.id=LastAssign.lastid      Left join QA_AgentDetails on process_'+@Pid+'_log.Log_id=QA_AgentDetails.Log_id  where  Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and process_'+@Pid+'_log.QA_Disposition = ''Quality Approve''  and QA_Assign_Agent='''+@Suserid+''''+ @SqlCon+';
					select count(1)[Quality Rework] from process_'+@Pid+'_master left join process_'+@Pid+'_log on process_'+@Pid+'_master.Master_Id = process_'+@Pid+'_log.Master_Id    join (select max(id)lastid,Log_Id from QA_Assign_Log group by Log_Id)LastAssign on  process_'+@Pid+'_log.Log_id=LastAssign.Log_id join QA_Assign_Log on QA_Assign_Log.id=LastAssign.lastid      Left join QA_AgentDetails on process_'+@Pid+'_log.Log_id=QA_AgentDetails.Log_id  where  Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and process_'+@Pid+'_log.QA_Disposition = ''Quality Rework''  and QA_Assign_Agent='''+@Suserid+''''+ @SqlCon+';
					select count(1)[Quality Hold] from process_'+@Pid+'_master left join process_'+@Pid+'_log on process_'+@Pid+'_master.Master_Id = process_'+@Pid+'_log.Master_Id		   join (select max(id)lastid,Log_Id from QA_Assign_Log group by Log_Id)LastAssign on  process_'+@Pid+'_log.Log_id=LastAssign.Log_id join QA_Assign_Log on QA_Assign_Log.id=LastAssign.lastid    Left Join QA_AgentDetails on process_'+@Pid+'_log.Log_id=QA_AgentDetails.Log_id  where  Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and process_'+@Pid+'_log.QA_Disposition = ''Quality Hold''  and QA_Assign_Agent='''+@Suserid+''''+ @SqlCon+';
					select count(1)[TL Approve] from process_'+@Pid+'_master left join process_'+@Pid+'_log on process_'+@Pid+'_master.Master_Id = process_'+@Pid+'_log.Master_Id		      join (select max(id)lastid,Log_Id from QA_Assign_Log group by Log_Id)LastAssign on  process_'+@Pid+'_log.Log_id=LastAssign.Log_id join QA_Assign_Log on QA_Assign_Log.id=LastAssign.lastid Left join QA_AgentDetails on process_'+@Pid+'_log.Log_id=QA_AgentDetails.Log_id  where  Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and process_'+@Pid+'_log.QA_Disposition = ''TL Approve'' and QA_Assign_Agent='''+@Suserid+''''+ @SqlCon+';
					select count(1)[Quality Reject] from process_'+@Pid+'_master left join process_'+@Pid+'_log on process_'+@Pid+'_master.Master_Id = process_'+@Pid+'_log.Master_Id     join (select max(id)lastid,Log_Id from QA_Assign_Log group by Log_Id)LastAssign on  process_'+@Pid+'_log.Log_id=LastAssign.Log_id join QA_Assign_Log on QA_Assign_Log.id=LastAssign.lastid     Left join QA_AgentDetails on process_'+@Pid+'_log.Log_id=QA_AgentDetails.Log_id  where  Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and process_'+@Pid+'_log.QA_Disposition = ''Quality Reject'' and QA_Assign_Agent='''+@Suserid+''''+ @SqlCon+'; 
					select count(1) [TL Hold] from process_' + @Pid + '_master left join process_' + @Pid + '_log on process_' + @Pid + '_master.Master_Id = process_' + @Pid + '_log.Master_Id left join QA_AgentDetails on process_' + @Pid + '_log.Log_id=QA_AgentDetails.Log_id  where Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and QA_Disposition = ''TL Hold''' + @SqlCon+ case when @QAAgent=''or @QAAgent='--select--'  then ' ' else ' and QA_Assign_Agent='''+@QAAgent+'''' end +' ;
					select count(1) [TL Reject] from process_' + @Pid + '_master left join process_' + @Pid + '_log on process_' + @Pid + '_master.Master_Id = process_' + @Pid + '_log.Master_Id left join QA_AgentDetails on process_' + @Pid + '_log.Log_id=QA_AgentDetails.Log_id  where Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and QA_Disposition = ''TL Reject''' + @SqlCon+ case when @QAAgent=''or @QAAgent='--select--'  then ' ' else ' and QA_Assign_Agent='''+@QAAgent+'''' end +' ; '
          

		 --select (@Sqlquery3)
	  --exec (@Sqlquery1)      
	end
	exec(@Sqlquery1)

end

GO

/****** Object:  StoredProcedure [dbo].[QA_Defect_Analysis_Report]    Script Date: 12/4/2019 5:21:29 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROC [dbo].[QA_Defect_Analysis_Report]
@tlid VARCHAR(30), @agentid VARCHAR(30), @qaid VARCHAR(30), @from VARCHAR(10), @to VARCHAR(10), @reporttype VARCHAR(30),@Process_id VARCHAR(10),@UserId VARCHAR(10)
AS BEGIN

DECLARE @SQL VARCHAR(MAX)

SELECT Question,SUM(CASE WHEN Rating='No' THEN 1 ELSE 0 END) [Defect_Count],
SUM (SUM(CASE WHEN Rating='No' THEN 1 ELSE 0 END)) OVER (ORDER BY (SUM(CASE WHEN Rating='No' THEN 1 ELSE 0 END))desc)[Cumulative_Frequency]
--,(SUM (SUM(CASE WHEN Rating='No' THEN 1 ELSE 0 END)) OVER (ORDER BY (SUM(CASE WHEN Rating='No' THEN 1 ELSE 0 END))desc)*100.00)/nullif(SUM(CASE WHEN Rating='No' THEN 1 ELSE 0 END),0)[per_Cumulative_Frequency]
into #temp
from QA_MonitoringFormDetails where Rating='No'
group by Question
order by (SUM(CASE WHEN Rating='No' THEN 1 ELSE 0 END)) desc

DECLARE @TOTAL int
select @TOTAL=SUM(Defect_Count) from #temp

select Question,[Defect_Count],[Cumulative_Frequency],([Cumulative_Frequency]*100)/@TOTAL [per_Cumulative_Frequency] from #temp

END


GO

/****** Object:  StoredProcedure [dbo].[QA_DQR_Report]    Script Date: 12/4/2019 5:21:29 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE Proc [dbo].[QA_DQR_Report]
@tlid VARCHAR(30), @agentid VARCHAR(30), @qaid VARCHAR(30), @from VARCHAR(10), @to VARCHAR(10), @reporttype VARCHAR(30),@Process_id VARCHAR(10),@UserId VARCHAR(10)
--QA_DQR_Report '1','','','','','','','',''
AS
BEGIN

Declare @Question nvarchar(max),@sql nvarchar(max),@Question_Name nvarchar(max),@Master_Field Varchar(max)
Select @Question= COALESCE (@Question + ',','') + 'MAX(CASE WHEN Question='''+Question+''' THEN Rating END) ' +QUOTENAME(Convert(varchar(128),Question)) from QA_Page_Master where process_id=1  order by id ASC
select @Question_Name=COALESCE(@Question_Name + ',','')+QUOTENAME(Convert(varchar(128),Question)) from QA_Page_Master where process_id=1  order by id ASC
Select @Master_Field= COALESCE (@Master_Field + ',','') + 'Pm.'+QUOTENAME(Field_Name) from Process_master where pid=@Process_id and Show_In_App=1 order by Order_No
--select @Master_Field
set @sql='
select Q_id,'+@Question+' into #temp from QA_MonitoringFormDetails group by  Q_id 

select '+@Master_Field+',QAD.Agent_Name,SrNo[Verified_By],CallerEMPID [Agent_Code],TLname [Team_Leader_Name],Date_of_Call [Call_date],
Date_of_Audit [Verifier_Date], Disposition [Call_Status],Auditor_Name [Online_Verifier_Name],QAD.Agent_Name[Call_taken_by],Date_of_Audit [Disposition_Date],'+@Question_Name+'
,External_performance[Score_without_Fatal],Internal_Performance[Score_with_Fatal]
from QA_AgentDetails QAD
left join Process_'+@Process_id+'_log pl on QAD.log_id=pl.Log_Id
left join Process_'+@Process_id+'_Master PM on pl.Master_Id=pm.Master_Id
left join #temp QMFD on qmfd.Q_id=qad.Q_id
--where qad.log_id=''1050'' and qad.Q_id=''15''
order by Date_of_Call desc'
exec (@sql)

END


GO

/****** Object:  StoredProcedure [dbo].[QA_FTR_Status_Report]    Script Date: 12/4/2019 5:21:29 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROC [dbo].[QA_FTR_Status_Report]
@tlid VARCHAR(30), @agentid VARCHAR(30), @qaid VARCHAR(30), @from VARCHAR(10), @to VARCHAR(10), @reporttype VARCHAR(30),@Process_id VARCHAR(10),@UserId VARCHAR(10)

AS BEGIN

select isnull(Agent_Name,'Total Count') Agent_Name,SUM(CASE WHEN FTR='Yes' THEN 1 ELSE 0 END)[FTR_Status] from QA_AgentDetails group by Agent_Name with Cube

select isnull(TLname,'Grand Total') TL_Name,SUM(CASE WHEN FTR='Yes' THEN 1 ELSE 0 END)[FTR_Status] from QA_AgentDetails group by TLname with Cube

SELECT isnull(TLname,'Grand Total') TL_Name,AVG(External_Performance) Average_of_Call_Score_per from QA_AgentDetails WHERE 1=1 group by TLname with Cube

END

GO

/****** Object:  StoredProcedure [dbo].[QA_LogDetails]    Script Date: 12/4/2019 5:21:29 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE PROC [dbo].[QA_LogDetails]

--QA_Manger '0','100','1','2018-10-10','','munish','TOTAL'
@LogID varchar(10)

AS BEGIN
DECLARE @sql varchar(max)

	set @sql='select Q_id,P_id,Agent_Name [Agent Name],Auditor_Name [Auditor Name],Customer_Name [Customer Name],Date_of_Call [Date of Call],Date_of_Audit [Date of Audit],Mobile [Mobile],Brand [Brand],Month,AHT,External_Performance [External Performance],Internal_Performance [Internal Performance],Fatal_Error_Call [Fatal Error Call],call_summary [Call Summary],areas_of_strength [Areas of strength],areas_of_development [Areas OfD evelopment],RecordingUrl [Recording Url],CallerEMPID [Caller EMPID],TLname [TL Name],Disposition [Disposition],ManagerName [Manager Name],LeadID [Lead ID],LeadStatusTime [Lead Status Time],Audit,CallDuration [Call Duration],Week,LeadSource [Lead Source],EvaluationMode [Evaluation Mode],ACPT,Log_Id,time_stamp from QA_AgentDetails where Log_Id = '+@LogID+''
	
	print(@sql)
	exec (@sql)
	
END


--[QA_LogDetails] '43692'

GO

/****** Object:  StoredProcedure [dbo].[QA_LogDetailsWithQ_ID]    Script Date: 12/4/2019 5:21:29 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE PROC [dbo].[QA_LogDetailsWithQ_ID]

--QA_Manger '0','100','1','2018-10-10','','munish','TOTAL'
@QID varchar(10)

AS BEGIN
DECLARE @sql varchar(max)

	set @sql='select 
Parameter_Name [Parameter Name],status,Question,Weightage,Applicable_Weightage [Applicable Weightage],Agents_Grade[Agents Grade],Rating,QA_comment[QA comment] from QA_MonitoringFormDetails where Q_ID = '+@QID+''
	
	print(@sql)
	exec (@sql)
	
END


--[QA_LogDetailsWithQ_ID] '43692'



GO

/****** Object:  StoredProcedure [dbo].[QA_Manger]    Script Date: 12/4/2019 5:21:29 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROC [dbo].[QA_Manger]

--QA_Manger '0','100','1','2019-11-01','','--select--','Quality Pending'
@pageindex varchar(20),@pagesize varchar(20),@Pid varchar(10),@FromDate varchar(10)='',@Todate varchar(19)='',@Agent_name varchar(70)='',@Type varchar(30)

AS BEGIN
DECLARE @sql varchar(max),@CusDispotion varchar(150),@StartDateFrom varchar(19)='2019-11-09',@List VARCHAR(max),@List1 VARCHAR(max),@datefield varchar(100);
if (@Agent_name= '--select--')
set @Agent_name=''

select @List=COALESCE(@List + ',', '')+ 'MasterData.'+Field_Name+QUOTENAME(Field_Description) from process_master where pid=@Pid and Show_In_App is not null and Show_In_App != 0

select @List1=COALESCE(@List1 + ',', '')+ 'Calllog.'+Field_Name+QUOTENAME(Field_Description) from process_log where pid=@Pid and Show_In_App is not null and Show_In_App != 0
set @List=@List+Case when isnull(@List1,'')!='' then ','+@List1 else '' end

set @datefield=case when  @Type='NOT ASSIGN' THEN ' ' WHEN @Type in('TOTAL','','Quality Pending') THEN ' Start_time >='''+@StartDateFrom+''' and convert(date,Start_Time)' ELSE ' Date_of_Audit >='''+@StartDateFrom+''' and convert(date,Date_of_Audit)' END
set @FromDate=case when @FromDate='' then convert(date,getdate()) else @FromDate end
set @Todate=case when @Todate='' then convert(date,getdate()) else @Todate end
select @CusDispotion=''''+replace(CallStatus,',',''',''')+'''' from QA_Page_Master1 where Process_id=@Pid

	set @sql='select Calllog.Log_id ,Calllog.Log_id[Log Id],'+@List+',QA_AgentDetails.Disposition,QA_Assign_Agent [QA Name],CallStatus,'+@Pid+'[PID]  into #tempp from process_'+@Pid+'_master MasterData 
	left join process_'+@Pid+'_log Calllog on Calllog.Master_Id=MasterData.master_id
	left join QA_AgentDetails on calllog.Log_id=QA_AgentDetails.Log_id
	 where  Calllog.CallStatus in('+@CusDispotion+') and (Q_id in (select max(Q_id) from QA_AgentDetails   group by Log_Id ) OR Q_id is null)   '
	
	if @Type='NOT ASSIGN'
	 	set @sql+='and Start_time>='''+@StartDateFrom +'''';
	 if @Type not in('NOT ASSIGN','total','','Quality Pending')
	 	set @sql+=' and Calllog.QA_Disposition = '''+@Type+''' '
	 if(@Type in('Quality Pending') )
	 set @sql+='and (Calllog.QA_Disposition is null )';

	 if(@Type in('')) AND (@Agent_name!='')
	 set @sql+='and (Calllog.QA_Disposition is null )';
	 else if(@Type='') AND (@Agent_name='')
	 set @sql+='and (Calllog.QA_Disposition is null and Status is null )';

	 if(@datefield!='')
	 set @sql+=' AND '+@datefield+' BETWEEN '''+@FromDate+''' AND '''+@Todate+''' '

	 else
	 set @sql+=' and Calllog.QA_Disposition is null   '

	  If (@Agent_name not in ('','--select--'))
		set @sql+= ' and QA_Assign_Agent ='''+@Agent_name+''' '
	 if(@Type='NOT ASSIGN' )
		Set @sql+=' and status is null '

 set @sql+='   ; Select * from #tempp order by Log_id
	offset '+@pageindex+' rows fetch next '+@pagesize+' row only 
	select count(*) from #tempp '
	print(@sql)
	exec (@sql)
	
END


--QA_Manger '0','100','1','2018-10-10','','munish','NOT ASSIGN'
--QA_Manger'0','100','1','','','--select--',''
--QA_Manger'0','100','1','','','--select--',''
--QA_Manger_AgentWise'0','100','1','','','munish',''

GO

/****** Object:  StoredProcedure [dbo].[QA_Manger_AgentWise]    Script Date: 12/4/2019 5:21:29 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



/****** Object:  StoredProcedure [dbo].[QA_Manger_AgentWise]    Script Date: 11/07/19 10:42:20 AM ******/


CREATE PROC [dbo].[QA_Manger_AgentWise]

--QA_Manger_AgentWise '0','100','1','','','munish',''
@pageindex varchar(20),@pagesize varchar(20),@Pid varchar(10),@FromDate varchar(10)='',@Todate varchar(19)='',@user varchar(20),@Type varchar(30)

AS BEGIN
DECLARE @sql varchar(max),@CusDispotion varchar(150),@PandingDateFrom varchar(19)='2019-11-09',@List VARCHAR(max),@List1 VARCHAR(max),@datefield varchar(50);

select @List=COALESCE(@List + ',', '')+ 'MasterData.'+Field_Name+QUOTENAME(Field_Description) from process_master where pid=1 and Show_In_App is not null and Show_In_App != 0

select @List1=COALESCE(@List1 + ',', '')+ 'Calllog.'+Field_Name+QUOTENAME(Field_Description) from process_log where pid=1 and Show_In_App is not null and Show_In_App != 0
set @List=@List+Case when isnull(@List1,'')!='' then ','+@List1 else '' end
select @CusDispotion=''''+replace(CallStatus,',',''',''')+'''' from QA_Page_Master1 where Process_id=@Pid

--select 'MasterData.'+Field_Name[Field_Name] into #ActiveField from process_master where pid=@Pid and Show_In_App is not null and Show_In_App != 0
--Union
--select 'Calllog.'+Field_Name[Field_Name] from process_log where pid=@Pid and Show_In_App is not null and Show_In_App != 0

--DECLARE @List VARCHAR(8000),@datefield varchar(50); SELECT @List = COALESCE(@List + ',', '') + CAST(Field_Name AS VARCHAR) FROM  #ActiveField ; 
--select @List

set @datefield=case when  @Type='PENDING' THEN '' WHEN @Type in('TOTAL','') THEN 'convert(date,QA_Assign_Log.Assign_Date)' ELSE 'convert(date,Date_of_Audit)' END
set @FromDate=case when @FromDate='' then convert(date,getdate()) else @FromDate end
set @Todate=case when @Todate='' then convert(date,getdate()) else @Todate end

	set @sql='select Calllog.Log_id,Calllog.Log_id[Log ID],'+@List+',QA_AgentDetails.Disposition,QA_Assign_Log.Assign_Date[Assign Date],CallStatus,'+@Pid+'[PID]  into #tempp from process_'+@Pid+'_master MasterData 
	join process_'+@Pid+'_log Calllog on Calllog.Master_Id=MasterData.master_id
	join (select max(id)lastid,Log_Id from QA_Assign_Log group by Log_Id)LastAssign 
		on  Calllog.Log_id=LastAssign.Log_id join QA_Assign_Log on QA_Assign_Log.id=LastAssign.lastid
	left join QA_AgentDetails on calllog.Log_id=QA_AgentDetails.Log_id
	 where  Calllog.CallStatus in('+@CusDispotion+') and (Q_id in (select max(Q_id) from QA_AgentDetails   group by Log_Id ) OR Q_id is null)  '
	 if @Type='NOT ASSIGN'
	 	set @sql+=' and QA_Assign_Log.Assign_Date>='''+@PandingDateFrom +''' ';
	 
	 if(@Type='')
	 set @sql+='and Calllog.QA_Disposition is null ';
	 if(@datefield!='')
	 set @sql+=' AND '+@datefield+' BETWEEN '''+@FromDate+''' AND '''+@Todate+''' '

	 else
	 set @sql+=' and Calllog.QA_Disposition is null   '

	 if(@datefield='convert(date,Date_of_Audit)')
	 set @sql+=' and Calllog.QA_Disposition = '''+@Type+''' '

	  If (@user  not in (''))
		set @sql+= ' and QA_Assign_Agent ='''+@user+''' '
		else
		if(@Type!='' and @Type!='total' AND @datefield!='convert(date,Date_of_Audit)')
		Set @sql+=' and status is null '



 set @sql+='  Select * from #tempp order by Log_id
	offset '+@pageindex+' rows fetch next '+@pagesize+' row only 
	select count(*) from #tempp '
	print(@sql)
	exec (@sql)
	
END




--@pageindex varchar(20),@pagesize varchar(20),@Pid varchar(10),@FromDate varchar(10)='',@Todate varchar(19)='',@user varchar(20),@Type varchar(30)

--AS BEGIN
--DECLARE @sql varchar(max)

--select 'MasterData.'+Field_Name[Field_Name] into #ActiveField from process_master where pid=@Pid and Show_In_App is not null and Show_In_App != 0
--Union
--select 'Calllog.'+Field_Name[Field_Name] from process_log where pid=@Pid and Show_In_App is not null and Show_In_App != 0

--DECLARE @List VARCHAR(8000);
--SELECT @List = COALESCE(@List + ',', '') + CAST(Field_Name AS VARCHAR) FROM  #ActiveField t1; 
----select @List
--	set @sql='select Calllog.Log_id,'+@List+',CallStatus into #tempp from process_'+@Pid+'_master MasterData 
--	left join process_'+@Pid+'_log Calllog on Calllog.Master_Id=MasterData.master_id 
--	left join QA_AgentDetails on calllog.Log_id=QA_AgentDetails.Log_id
--	where  CallStatus in('+@CusDispotion+') and QA_Assign_Agent ='''+@user+''' '


--	if (@Type in ('TOTAL','PENDING',''))
--	 BEGIN
--		 if (@Type!='PENDING' and @Type in ('TOTAL',''))
--		 Begin
--			If (@FromDate='' and @Todate='')
--			begin
--				set @sql+= ' and convert(date,Start_Time)=convert(date,getdate()) '
--			end
--			else If (@FromDate!='' and @Todate='')
--				begin
--					set @sql+= ' and Convert(date,Start_time)>=Convert(date,'''+@FromDate+''')'
--				end
--				Else If (@FromDate='' and @Todate!='')
--				begin
--					set @sql+= ' and Convert(date,Start_time)<=Convert(date,'''+@Todate+''')'
--				end
		 
--		 End
--		 else If (@FromDate!='' and @Todate='')
--				begin
--					set @sql+= ' and Convert(date,Start_time)>=Convert(date,'''+@FromDate+''')'
--				end
--				Else If (@FromDate='' and @Todate!='')
--				begin
--					set @sql+= ' and Convert(date,Start_time)<=Convert(date,'''+@Todate+''')'
--				end
--	 END
	
--	else
--	begin
--	If @FromDate='' and @Todate=''
--		begin
--			set @sql+= ' and convert(date,Date_of_Audit)=convert(date,getdate()) '
--		end	
--		else 
--		begin
--			If @FromDate!=''
--			begin
--				set @sql+= ' and Convert(date,Date_of_Audit)>=Convert(date,'''+@FromDate+''')'
--			end
--			If @Todate!=''
--			begin
--				set @sql+= ' and Convert(date,Date_of_Audit)<=Convert(date,'''+@Todate+''')'
--			end		
--		end
--	end
--	 if @Type='TOTAL'
--	 begin
--	 set @sql+=' and (Q_id in (select max(Q_id) from QA_AgentDetails   group by Log_Id ) OR Q_id is null) '
--	 end
--	 else if @Type='PENDING'
--	 begin
--	  set @sql+='and  ( QA_Disposition is null )'
--	 end
--	 else if @Type='QUALITY APPROVE'
--	 begin 
--	  set @sql+='and Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and QA_Disposition = ''Quality Approve'''
--	 end
--	 else if @Type='QUALITY REWORK'
--	 begin 
--	  set @sql+='and Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and QA_Disposition = ''Quality Rework'''
--	 end
--	 else if @Type='QUALITY HOLD'
--	 begin
--	 set @sql+='and Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and QA_Disposition = ''Quality Hold'''
--	  end

--	  else if @Type='REJECT'
--	 begin
--	 set @sql+='and Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and QA_Disposition = ''Quality Reject'''
--	  end

--	  else if @Type='TL APPROVE'
--	 begin
--	 set @sql+='and Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and QA_Disposition = ''TL Approve'''
--	  end
--	 else
--	 begin
--		set @sql+= ' and (Q_id in (select max(Q_id) from QA_AgentDetails   group by Log_Id ) OR Q_id is null)  and (QA_Disposition is null or QA_Disposition='''' or QA_Disposition=''TL Hold'' or QA_Disposition=''TL Approve'' ) and status is null  and CallStatus is not null and CallStatus !='''''
--	 end

--	set @sql+='select * from #tempp order by Log_id
--	offset '+@pageindex+' rows fetch next '+@pagesize+' row only 
--	select count(*) from #tempp '
--	print(@sql)
--	exec (@sql)
	
--END

--QA_Manger_AgentWise '0','100','1','Parveen'
--QA_Manger_AgentWise'0','100','1','','','munish','TOTAL'
--select * from process_1_log where Log_Id='152104'

--Update Process_1_Log set QA_Disposition='Quality Approve' where Log_id='7144'

--select *,CallStatus from process_1_master MasterData
--left join process_1_log Calllog on Calllog.Master_Id=MasterData.master_id
--where  CallStatus in('+@CusDispotion+') and status is not null and CallStatus is not null and CallStatus !='' and QA_Assign_Agent='munish'



GO

/****** Object:  StoredProcedure [dbo].[QA_Manger_TLWise]    Script Date: 12/4/2019 5:21:29 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




CREATE PROC [dbo].[QA_Manger_TLWise]


--QA_Manger_TLWise '0','100','1','','','20',''
@pageindex varchar(20),@pagesize varchar(20),@Pid varchar(10),@FromDate varchar(10)='',@Todate varchar(19)='',@user varchar(20),@Type varchar(30)

AS BEGIN
DECLARE @sql varchar(max),@CusDispotion varchar(150),@PandingDateFrom varchar(19)='2019-11-09',@List VARCHAR(max),@List1 VARCHAR(max),@datefield varchar(100),@cond varchar(200);

select @List=COALESCE(@List + ',', '')+ 'MasterData.'+Field_Name+QUOTENAME(Field_Description) from process_master where pid=1 and Show_In_App is not null and Show_In_App != 0

select @List1=COALESCE(@List1 + ',', '')+ 'Calllog.'+Field_Name+QUOTENAME(Field_Description) from process_log where pid=1 and Show_In_App is not null and Show_In_App != 0
set @List=@List+Case when isnull(@List1,'')!='' then ','+@List1 else '' end
select @CusDispotion=''''+replace(CallStatus,',',''',''')+'''' from QA_Page_Master1 where Process_id=@Pid

--DECLARE @List VARCHAR(8000),@datefield varchar(50),@cond varchar(200); SELECT @List = COALESCE(@List + ',', '') + CAST(Field_Name AS VARCHAR) FROM  #ActiveField ; 
--select @List

--set @datefield=case when  @Type='PENDING' THEN '' WHEN @Type in('TOTAL','') THEN 'convert(date,Start_Time)' ELSE 'convert(date,Date_of_Audit)' END
set @datefield='convert(date,Date_of_Audit)'
--set @datefield=case when  @Type='PENDING' THEN '' WHEN @Type in('TOTAL','') THEN  'convert(date,Date_of_Audit)' END
set @FromDate=case when @FromDate='' then convert(date,getdate()) else @FromDate end
set @Todate=case when @Todate='' then convert(date,getdate()) else @Todate end
set @cond=case when @Type='TOTAL'  then  ' and (Calllog.QA_Disposition=''Quality Rework'' or  Calllog.QA_Disposition=''Quality Hold'')' 
when @Type='PENDING'  then  ' and (Calllog.QA_Disposition=''Quality Rework'' or  Calllog.QA_Disposition=''Quality Hold'') and Team_Leader_Id ='''+@user+''''
when @Type='QUALITY REWORK'  then  '  and Calllog.QA_Disposition = ''Quality Rework'' and (Calllog.QA_Disposition=''Quality Rework'' or  Calllog.QA_Disposition=''Quality Hold'')'

when @Type='QUALITY HOLD'  then  ' and Calllog.QA_Disposition = ''Quality Hold'' and (Calllog.QA_Disposition=''Quality Rework'' or  Calllog.QA_Disposition=''Quality Hold'')'

when @Type='TL APPROVE'  then  '  and Calllog.QA_Disposition = ''TL Approve'''
when @Type='TL Hold'  then  '  and Calllog.QA_Disposition = ''TL Hold'''
when @Type='TL REJECT'  then  ' and Calllog.QA_Disposition = ''TL Reject'''


else  ' and Calllog.QA_Disposition != '''' and (Calllog.QA_Disposition=''Quality Rework'' or  Calllog.QA_Disposition=''Quality Hold'')' end

	set @sql='select Calllog.Log_id,Calllog.Log_id[Log ID],'+@List+',QA_AgentDetails.Disposition,QA_Assign_Log.Assign_Date[Assign Date],Convert(varchar,QA_AgentDetails.Date_of_Audit,121)[Date of Audit],CallStatus,'+@Pid+'[PID] into #tempp from process_'+@Pid+'_master MasterData 
	join process_'+@Pid+'_log Calllog on Calllog.Master_Id=MasterData.master_id
	Left join (select max(id)lastid,Log_Id from QA_Assign_Log group by Log_Id)LastAssign 
		on  Calllog.Log_id=LastAssign.Log_id 
		Left join QA_Assign_Log on QA_Assign_Log.id=LastAssign.lastid
	left join QA_AgentDetails on calllog.Log_id=QA_AgentDetails.Log_id
	 where  QA_AgentDetails.Date_of_Audit>='''+@PandingDateFrom +''' and Calllog.CallStatus in('+@CusDispotion+') and (Q_id in (select max(Q_id) from QA_AgentDetails   group by Log_Id ) OR Q_id is null)  '
	 
	 if(@Type='')
	 set @sql+='';
	 if(@datefield!='' AND @Type!='PENDING')
	 set @sql+=' AND '+@datefield+' BETWEEN '''+@FromDate+''' AND '''+@Todate+''' '

	 ----else
	 --set @sql+=' and QA_Disposition is null   '
--' and QA_Disposition = '''+@Type+''' 
	 if(@datefield='convert(date,Date_of_Audit)')
	 set @sql+=@cond

	 
	  If (@user  not in (''))
		set @sql+= ' and Team_Leader_Id ='''+@user+''' '
		else
		if(@Type!='' and @Type!='total' AND @datefield!='convert(date,Date_of_Audit)')
		Set @sql+=' and (status is null or status = 1) '



 set @sql+='  Select * from #tempp order by Log_id
	offset '+@pageindex+' rows fetch next '+@pagesize+' row only 
	select count(*) from #tempp '
	print(@sql)
	exec (@sql)
	
END

--@pageindex varchar(20),@pagesize varchar(20),@Pid varchar(10),@FromDate varchar(10)='',@Todate varchar(19)='',@user varchar(20),@Type varchar(30)
--AS BEGIN
--DECLARE @sql varchar(max)

--select 'MasterData.'+Field_Name[Field_Name] into #ActiveField from process_master where pid=@Pid and Show_In_App is not null and Show_In_App != 0
--Union
--select 'Calllog.'+Field_Name[Field_Name] from process_log where pid=@Pid and Show_In_App is not null and Show_In_App != 0

--DECLARE @List VARCHAR(8000);
--SELECT @List = COALESCE(@List + ',', '') + CAST(Field_Name AS VARCHAR) FROM  #ActiveField t1; 
----select @List
--	set @sql='select Calllog.Log_id,'+@List+',CallStatus into #tempp from process_'+@Pid+'_master MasterData 
--	left join process_'+@Pid+'_log Calllog on Calllog.Master_Id=MasterData.master_id 
--	left join QA_AgentDetails on calllog.Log_id=QA_AgentDetails.Log_id
--	where  CallStatus in('+@CusDispotion+')  and Team_Leader_Id='''+@user+'''' 
--	if @Type!='PENDING'
--	Begin 
--	 If @FromDate='' and @Todate=''
--	 begin
--		set @sql+= ' and convert(date,Date_of_Audit)=convert(date,getdate()) '
--	 end
--	 else 
--	 begin
--		If @FromDate!=''
--		begin
--			set @sql+= ' and Convert(date,Date_of_Audit)>=Convert(date,'''+@FromDate+''')'
--		end
--		If @Todate!=''
--		begin
--			set @sql+= ' and Convert(date,Date_of_Audit)<=Convert(date,'''+@Todate+''')'
--		end
--	 end
--	 END

--	 If @Type='TOTAL'
--	 begin
--	 set @sql+= ' and (Q_id in (select max(Q_id) from QA_AgentDetails   group by Log_Id ) OR Q_id is null)  and(QA_Disposition=''Quality Rework'' or  QA_Disposition=''Quality Hold'')'
--	 end

--	 else if @Type='PENDING'
--	  begin
--	 set @sql+= ' and (Q_id in (select max(Q_id) from QA_AgentDetails   group by Log_Id ) OR Q_id is null) and (QA_Disposition=''Quality Rework'' or  QA_Disposition=''Quality Hold'')'
--	 end

--	 else if @Type='QUALITY REWORK'
--	  begin
--	 set @sql+= ' and (Q_id in (select max(Q_id) from QA_AgentDetails   group by Log_Id ) OR Q_id is null) and Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and QA_Disposition = ''Quality Rework'' and (QA_Disposition=''Quality Rework'' or  QA_Disposition=''Quality Hold'')'
--	 end

--	 else if @Type='QUALITY HOLD'
--	  begin
--	 set @sql+= ' and (Q_id in (select max(Q_id) from QA_AgentDetails   group by Log_Id ) OR Q_id is null) and Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and QA_Disposition = ''Quality Hold'' and (QA_Disposition=''Quality Rework'' or  QA_Disposition=''Quality Hold'')'
--	 end

--	 else if @Type='TL APPROVE'
--	  begin
--	 set @sql+= ' and (Q_id in (select max(Q_id) from QA_AgentDetails   group by Log_Id ) OR Q_id is null) and Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and QA_Disposition = ''TL Approve'''
--	 end

--	 else if @Type='TL HOLD'
--	  begin
--	 set @sql+= ' and (Q_id in (select max(Q_id) from QA_AgentDetails   group by Log_Id ) OR Q_id is null) and Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and QA_Disposition = ''TL Hold'''
--	 end

--	 else if @Type='TL REJECT'
--	  begin
--	 set @sql+= ' and (Q_id in (select max(Q_id) from QA_AgentDetails   group by Log_Id ) OR Q_id is null) and Q_id in (select max(Q_id) from QA_AgentDetails group by Log_Id) and QA_Disposition = ''TL Reject'''
--	 end

--	 else
--	 begin
--		set @sql+= ' and (status is null or status=1) and (QA_Disposition=''Quality Rework'' or  QA_Disposition=''Quality Hold'') and  CallStatus is not null and CallStatus !='''''
--	 end
--	set @sql+='select * from #tempp order by Log_id
--	offset '+@pageindex+' rows fetch next '+@pagesize+' row only 
--	select count(*) from #tempp '
--	print(@sql)
--	exec (@sql)
	
--END


--QA_Manger_TLWise '0','100','1','','','17','TOTAL'
--QA_Manger_AgentWise'0','100','1','','','17'
--select * from process_1_log where Log_Id='152104'
--QA_Manger_TLWise'0','100','1','','','17',''

--Update Process_1_Log set QA_Disposition='Quality Approve' where Log_id='7144'

--select *,CallStatus from process_1_master MasterData
--left join process_1_log Calllog on Calllog.Master_Id=MasterData.master_id
--where  CallStatus in('+@CusDispotion+') and status is not null and CallStatus is not null and CallStatus !='' and QA_Assign_Agent='munish'



GO

/****** Object:  StoredProcedure [dbo].[QA_MonitoringDetail]    Script Date: 12/4/2019 5:21:29 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROC [dbo].[QA_MonitoringDetail]
@Pid varchar(10),@log_id varchar(70)=''

AS BEGIN
DECLARE @sql varchar(max),@List VARCHAR(max),@List1 VARCHAR(max);


select @List=COALESCE(@List + ',', '')+ 'MasterData.'+Field_Name+QUOTENAME(Field_Description) from process_master where pid=1 and Show_In_App is not null and Show_In_App != 0

select @List1=COALESCE(@List1 + ',', '')+ 'Calllog.'+Field_Name+QUOTENAME(Field_Description) from process_log where pid=1 and Show_In_App is not null and Show_In_App != 0
set @List=@List+Case when isnull(@List1,'')!='' then ','+@List1 else '' end
 --SELECT @List = COALESCE(@List + ',', '') + CAST(Field_Name AS VARCHAR) FROM  #ActiveField; 
--select @List

	set @sql='select  '+@List+' into #tempp from process_'+@Pid+'_master MasterData 
	left join process_'+@Pid+'_log Calllog on Calllog.Master_Id=MasterData.master_id
	 where '
	 begin
	 set @sql+= 'log_id ='''+@log_id+''''
	 end
	set @sql+='Select * from #tempp'
	--offset '+@pageindex+' rows fetch next '+@pagesize+' row only 
	--select count(*) from #tempp 
	print(@sql)
	exec (@sql)
END

GO

/****** Object:  StoredProcedure [dbo].[QA_Performers_Report]    Script Date: 12/4/2019 5:21:29 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROC [dbo].[QA_Performers_Report]
@tlid VARCHAR(30)='', @agentid VARCHAR(30)='', @qaid VARCHAR(30)='', @from VARCHAR(10)='', @to VARCHAR(10)='', @reporttype VARCHAR(30)='',@Process_id VARCHAR(10)='',@UserId VARCHAR(10)=''

AS
BEGIN

DECLARE @RANK table (SrNo int)
INSERT INTO @RANK(SrNo) VALUES ('1'),('2'),('3')

DECLARE @Performers table (SrNo int,Agent_Name varchar(50),CallScore varchar(5))
INSERT INTO @Performers (SrNo,Agent_Name,CallScore)
select top 3 ROW_NUMBER() OVER (Order By Avg(External_Performance) desc) SrNo,Agent_Name,Avg(External_Performance)[CallScore]
from QA_AgentDetails
group by Agent_Name 
Order by Avg(External_Performance) desc

select r.SrNo,isnull(Agent_Name,'')Agent_Name,isnull(CallScore,'0')CallScore from @RANK r  Left Join @Performers p on r.SrNo=p.SrNo


DECLARE @RANK_TL table (SrNo int)
INSERT INTO @RANK_TL(SrNo) VALUES ('1')

DECLARE @Performers_TL table (SrNo int,TLname varchar(50),CallScore varchar(5))
INSERT INTO @Performers_TL (SrNo,TLname,CallScore)
select  top 1 ROW_NUMBER() OVER (Order By Avg(External_Performance) desc) SrNo,TLname,Avg(External_Performance)[CallScore] from QA_AgentDetails group by TLname Order by Avg(External_Performance) desc

select r.SrNo,isnull(TLname,'')TLname,isnull(CallScore,'0')CallScore from @RANK_TL r  Left Join @Performers_TL p on r.SrNo=p.SrNo

END
GO

/****** Object:  StoredProcedure [dbo].[QA_Sale_Wise_Report]    Script Date: 12/4/2019 5:21:29 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROC [dbo].[QA_Sale_Wise_Report]
@tlid VARCHAR(30), @agentid VARCHAR(30), @qaid VARCHAR(30), @from VARCHAR(10), @to VARCHAR(10), @reporttype VARCHAR(30),@Process_id VARCHAR(10),@UserId VARCHAR(10)
AS BEGIN

select CASE WHEN GROUPING(pl.Agent_Name)=1 THEN 'Total Sale Count' ELSE pl.Agent_Name END Agent_Name,SUM(CASE WHEN CallStatus='Resolved' THEN 1 ELSE 0 END) [Total_Sale] 
from Process_1_Log pl
LEFT JOIN Admin_Agent_Master aam on aam.Agent_Name=pl.Agent_Name
where ISNULL(pl.Agent_Name,'') not in ('','admin')
group by pl.Agent_Name with cube
--where CallStatus='Resolved'

select CASE WHEN GROUPING(User_Name)=1 THEN 'Grand Total' ELSE User_Name END [TL_Name],SUM(CASE WHEN CallStatus='Resolved' THEN 1 ELSE 0 END) [Total_Sale] 
from Process_1_Log join User_Master on Team_Leader_Id=User_Id
where ISNULL(Agent_Name,'') not in ('','admin')
group by User_Name with cube

END

GO

/****** Object:  StoredProcedure [dbo].[QA_Team_Wise_Score_Report]    Script Date: 12/4/2019 5:21:29 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE PROC [dbo].[QA_Team_Wise_Score_Report]
@tlid VARCHAR(30), @agentid VARCHAR(30), @qaid VARCHAR(30), @from VARCHAR(10), @to VARCHAR(10), @reporttype VARCHAR(30),@Process_id VARCHAR(10),@UserId VARCHAR(10)
AS BEGIN

DECLARE @sql NVARCHAR(MAX)='';

SET @sql=' SELECT TLname TL_Name,AVG(External_Performance) Average_of_Call_Score_per from QA_AgentDetails WHERE 1=1  '
	IF(@tlid!='0')
	SET @sql+=' AND TLname='''+@tlid+''' ';
	IF(@agentid!='0')
	SET @sql+=' AND Agent_Name='''+@agentid+''' ';
	IF(@qaid!='0')
	SET @sql+=' AND Auditor_Name='''+@qaid+''' ';
	SET @sql+=CASE WHEN @from='' THEN 'AND CONVERT(DATE,Date_of_Audit) BETWEEN CONVERT(DATE,GETDATE())' ELSE ' AND CONVERT(DATE,Date_of_Audit) BETWEEN '''+@from+''' ' END;
	SET @sql+=CASE WHEN @to='' THEN 'AND CONVERT(DATE,GETDATE())' ELSE ' AND '''+@to+''' ' END;

SET @sql+=' GROUP BY TLname ';

EXECUTE sp_executesql @SQL

END

GO

/****** Object:  StoredProcedure [dbo].[QA_Telecaller_WISE_REPORT]    Script Date: 12/4/2019 5:21:29 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE PROC [dbo].[QA_Telecaller_WISE_REPORT]
@tlid VARCHAR(30), @agentid VARCHAR(30), @qaid VARCHAR(30), @from VARCHAR(10), @to VARCHAR(10), @reporttype VARCHAR(30),@Process_id VARCHAR(10),@UserId VARCHAR(10)
AS BEGIN

DECLARE @sql NVARCHAR(MAX)='';

SET @sql='select convert(varchar(10),start_time,103)[Date],pl.Agent_Name,sum(case when CallStatus=''Resolved'' then 1 else 0 end)[Total leads generated] ,
sum(case when isnull(QA_Disposition,'''')!='''' then 1 else 0 end)[Total leads audited],
sum(case when QA_Disposition is null  then 1 else 0 end)[Audit pending],
sum(case when QA_Disposition=''Quality Approve'' then 1 else 0 end)[Audit ok],
sum(case when QA_Disposition=''Quality Rework'' then 1 else 0 end)[Rework],
sum(case when QA_Disposition=''Quality Reject'' then 1 else 0 end)[Reject]
from process_'+@Process_id+'_log pl
left join QA_AgentDetails qa on pl.Log_Id=qa.Log_Id
where isnull(pl.Agent_Name,'''')!='''' and (Q_id in (select max(Q_id) from QA_AgentDetails   group by Log_Id ) OR Q_id is null) '
	IF(@tlid!='0')
	SET @sql+=' AND qa.TLname='''+@tlid+'''';
	IF(@agentid!='0')
	SET @sql+=' AND pl.Agent_Name='''+@agentid+''' ';
	IF(@qaid!='0')
	SET @sql+='';
	SET @sql+=CASE WHEN @from='' THEN 'AND CONVERT(DATE,Start_Time) BETWEEN CONVERT(DATE,GETDATE())' ELSE ' AND CONVERT(DATE,Start_Time) BETWEEN '''+@from+''' ' END;
	SET @sql+=CASE WHEN @to='' THEN 'AND CONVERT(DATE,GETDATE())' ELSE ' AND '''+@to+''' ' END;

SET @sql+='group by pl.Agent_Name,convert(varchar(10),start_time,103) ';

EXECUTE sp_executesql @SQL
print (@sql)

END

--QA_Telecaller_WISE_REPORT '0','bharat','0','2019-11-01','','0'

GO

/****** Object:  StoredProcedure [dbo].[QA_TL_WISE_REPORT]    Script Date: 12/4/2019 5:21:29 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROC [dbo].[QA_TL_WISE_REPORT]
@tlid VARCHAR(30), @agentid VARCHAR(30), @qaid VARCHAR(30), @from VARCHAR(15), @to VARCHAR(15), @reporttype VARCHAR(30),@Process_id VARCHAR(10),@UserId VARCHAR(10)
AS BEGIN

DECLARE @sql NVARCHAR(MAX)='';

SET @sql='select convert(varchar,Date_of_Audit,103)[Date], TLname,
sum(case when Disposition is not null then 1 else 0 end)[Total leads generated] , 
sum(case when isnull(Disposition,'''')!='''' then 1 else 0 end)[Total leads audited],
sum(case when Disposition in(''Quality Rework Quality Reject'') then 1 else 0 end)[Audit pending],
sum(case when Disposition=''Quality Approve'' then 1 else 0 end)[Audit ok],
sum(case when Disposition=''Quality Rework'' then 1 else 0 end)[Rework],
sum(case when Disposition=''Quality Reject'' then 1 else 0 end)[Reject]
from QA_AgentDetails
where P_id='''+@Process_id+''' and (Q_id in (select max(Q_id) from QA_AgentDetails   group by Log_Id ) OR Q_id is null) '
	IF(@tlid!='0')
	SET @sql+=' AND TLname='''+@tlid+''' ';
	IF(@agentid!='0')
	SET @sql+=' AND Agent_Name='''+@agentid+''' ';
	IF(@qaid!='0')
	SET @sql+=' AND Auditor_Name='''+@qaid+''' ';
	SET @sql+=CASE WHEN @from='' THEN 'AND CONVERT(DATE,Date_of_Audit) BETWEEN CONVERT(DATE,GETDATE())' ELSE ' AND CONVERT(DATE,Date_of_Audit) BETWEEN '''+@from+''' ' END;
	SET @sql+=CASE WHEN @to='' THEN 'AND CONVERT(DATE,GETDATE())' ELSE ' AND '''+@to+''' ' END;

SET @sql+='group by TLname,convert(varchar,Date_of_Audit,103)';

EXECUTE sp_executesql @SQL
print(@sql)
END

--QA_TL_WISE_REPORT 'raj','bharat','0','2019-11-05','','0'

GO

